import Static
from biz.mysql.io.BaseSql import sql


class Sept_wangui(sql):

    def __init__(self):
        self.groupby=' OrgCode,yx '

    def get(self) ->str:
        sql='''
        SELECT  T_DW.OrgCode '院系码',T_DW.yx '院系',COALESCE(count,0) AS '晚归学生数'
FROM  (SELECT dwm AS OrgCode,dwmc AS yx FROM T_DW WHERE dwlx="院系" ) T_DW
LEFT JOIN 
(
       {before_sql}
) zong
ON  T_DW.OrgCode=zong.OrgCode
ORDER BY T_DW.OrgCode,T_DW.yx
        '''.format(before_sql=self._before_sql(self.groupby))
        return sql

    def _before_sql(self,metric):
        sql='''
         SELECT {},COUNT(wg.xh) AS count
        FROM
        (
                SELECT DISTINCT XH_T_XMSSMJ.xh
                FROM
                (
                        SELECT DATE(sj) AS rq,TIME(sj) AS sj,xh,xwlx,"宿舍" AS sbmc
                        FROM XH_T_SSMJ
                        
                        UNION ALL
                        
                        SELECT DATE(rq) AS rq,TIME(sj) AS sj,xh,xwlx,"校门" AS sbmc
                        FROM XH_T_XMMJ
                )
                `XH_T_XMSSMJ` 
                WHERE sj<"06:00:00" OR sj>"23:00:00"  AND 
                        month(rq)={}
        ) wg
        INNER JOIN
        (
                SELECT xh,xs.pycc,xs.yxm as orgCode,
                            (SELECT dwmc FROM T_DW WHERE T_DW.dwm=xs.yxm ) as yx
                from T_XSJBXX xs
        ) xs ON wg.xh=xs.xh
        WHERE 1=1 
        GROUP BY {}
        '''.format(metric,Static.MONTH_NUM,metric)
        print(">>>>>{}.{}>>>{}".format(self, 'unique_sql', sql))
        return sql